use super::entities::{Constraint, HyperTable, TableColumn};
use sqlx::Row;
use sqlx::{postgres::PgRow, PgConnection};

use crate::models::AppResult;
use sqlx::postgres::types::Oid as PgOId;

/// 查询约束
///
/// [PostgreSQL: Documentation: 15: 53.13. pg\_constraint](https://www.postgresql.org/docs/current/catalog-pg-constraint.html)
pub async fn query_constraints(
    conn: &mut PgConnection,
    schema_id: &PgOId,
) -> AppResult<Vec<Constraint>> {
    // or contype='u'
    // let sql="SELECT a.conname,a.contype,a.conkey,b.oid,b.relname FROM pg_constraint a LEFT join pg_class b ON b.oid = a.conrelid WHERE contype='p' or contype='u' ORDER BY contype";
    let sql = "SELECT oid,conname,contype,condeferrable,condeferred,convalidated,conrelid,contypid,conindid,conparentid,confrelid,confupdtype,confdeltype,confmatchtype,conislocal,coninhcount,connoinherit,conkey,confkey,conpfeqop,conppeqop,conffeqop,conexclop,pg_get_expr(conbin,conrelid) as conbin_text FROM pg_constraint WHERE connamespace=$1";
    Ok(sqlx::query_as::<sqlx::Postgres, Constraint>(sql)
        .bind(schema_id)
        .fetch_all(conn)
        .await?)
}

/// 查询结构数据
pub async fn query_structs(
    conn: &mut PgConnection,
    db_name: &str,
    schema: &str,
    schema_id: &PgOId,
) -> AppResult<Vec<PgRow>> {
    let sql="SELECT b.oid as table_oid,a.table_catalog,a.table_schema,a.table_name,a.column_name,a.ordinal_position,a.column_default,a.is_nullable,a.data_type,a.character_maximum_length,a.character_octet_length,a.numeric_precision,a.numeric_precision_radix,a.numeric_scale,a.datetime_precision,a.udt_name,c.spcname FROM information_schema.columns a LEFT JOIN pg_class b on b.relname = a.table_name and b.relnamespace=$1 LEFT JOIN pg_tablespace c ON c.oid = b.reltablespace WHERE a.table_catalog=$2 AND a.table_schema=$3 ORDER BY a.ordinal_position";
    Ok(sqlx::query(sql)
        .bind(schema_id)
        .bind(db_name)
        .bind(schema)
        .fetch_all(conn)
        .await?)
}

/// 查询出表的所有列的数据
pub async fn query_table_columns(
    conn: &mut PgConnection,
    database: &str,
    schema: &str,
    table: &str,
) -> AppResult<Vec<TableColumn>> {
    let sql =
    "SELECT \"column_name\",ordinal_position,column_default,is_nullable,data_type,character_maximum_length,character_octet_length,numeric_precision,numeric_precision_radix,numeric_scale,datetime_precision,interval_type,interval_precision,\"character_set_catalog\",\"character_set_schema\",\"character_set_name\",\"collation_catalog\",\"collation_schema\",\"collation_name\",\"udt_catalog\",\"udt_schema\",\"udt_name\" FROM \"information_schema\".\"columns\" a WHERE \"table_catalog\"=$1 AND \"table_schema\"=$2 AND \"table_name\"=$3 ORDER BY ordinal_position";
    let datas = sqlx::query(sql)
        .bind(database)
        .bind(schema)
        .bind(table)
        .fetch_all(conn)
        .await?;
    let mut result = vec![];
    for data in datas {
        result.push(TableColumn::from_row(data));
    }
    Ok(result)
}

/// 查询扩展
pub async fn query_extensions(conn: &mut PgConnection) -> AppResult<Vec<PgRow>> {
    Ok(
        sqlx::query("select name from pg_available_extensions where installed_version is not null")
            .fetch_all(conn)
            .await?,
    )
}

/// 查询超表
pub async fn query_hyper_tables(conn: &mut PgConnection) -> AppResult<Vec<HyperTable>> {
    let rows = sqlx::query(
            "SELECT a.\"schema_name\",a.\"table_name\",b.\"column_name\",b.\"interval_length\" FROM \"_timescaledb_catalog\".\"hypertable\" a LEFT JOIN \"_timescaledb_catalog\".\"dimension\" b ON b.\"hypertable_id\" = a.\"id\""
        ).fetch_all(conn)
        .await?;
    let mut result = vec![];
    for row in rows {
        result.push(HyperTable::from_row(row));
    }
    Ok(result)
}

/// 获取索引
pub async fn query_indexes(conn: &mut PgConnection, schema: &str) -> AppResult<Vec<PgRow>> {
    Ok(sqlx::query("select \"tablename\",\"indexname\",\"indexdef\" FROM \"pg_indexes\" where \"schemaname\"=$1 and \"indexdef\" NOT LIKE 'CREATE UNIQUE INDEX%'").bind(schema).fetch_all(conn).await?)
}

/// 通过 pg_class 表的 relkind = 'p' 表示为分区表
/// 通过 pg_class 表的 relkind = 'r' and relispartition = true 表示为分区表的子表
/// 分区表和主表的关系存储在 pg_inherits 表中，关系为 pg_inherits.inhparent = pg_class.oid，其中  pg_inherits.inhrelid = pg_class.oid
pub async fn query_partiotion_table_names(
    conn: &mut PgConnection,
    schema_id: &PgOId,
) -> AppResult<Vec<String>> {
    let rows = sqlx::query( "select relname from pg_catalog.pg_class where relispartition=true and relam=2 and relnamespace=$1").bind(schema_id).fetch_all(conn).await?;
    Ok(rows
        .iter()
        .map(|x| x.get::<String, &str>("relname"))
        .collect())
}

/// 查询出所有的表
pub async fn query_tables(
    conn: &mut PgConnection,
    schema_id: &PgOId,
) -> AppResult<Vec<super::entities::Table>> {
    let sql="SELECT a.oid,a.relname,a.relowner,b.spcname FROM pg_class a LEFT JOIN pg_tablespace b ON b.oid = a.reltablespace WHERE a.relnamespace = $1 and a.relispartition=false and a.reltype!=0 ORDER BY relname";
    let datas = sqlx::query(sql).bind(schema_id).fetch_all(conn).await?;
    let mut result = vec![];
    for data in datas {
        result.push(super::entities::Table::from_row(data));
    }
    Ok(result)
}

/// 查询出对应的 schema_id
pub async fn query_schema_id(conn: &mut PgConnection, schema: &str) -> AppResult<PgOId> {
    let sql = "SELECT oid FROM pg_namespace WHERE nspname=$1";
    let row = sqlx::query(sql).bind(schema).fetch_one(conn).await?;
    return Ok(row.get("oid"));
}
